﻿using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

using MicroDb.Resolves;

using Microsoft.Extensions.Primitives;

using Newtonsoft.Json.Linq;

namespace MicroDb.MySql
{
    public class MySqlResolver : SqlResolver
    {


        public override string TransformField(string name)
        {
            return $"`{MicroDbHelper.ToUnderlineName(name)}`";
        }

        public override string IfNull(string first, string next)
        {
            return string.Format(" IFNULL({0},{1}) ", first, next);
        }

        /// <summary>
        /// 类型转换
        /// </summary>
        /// <param name="value"></param>
        /// <param name="convertType"></param>
        /// <returns></returns>
        public override string ConvertType(string value, Type convertType)
        {
            switch (convertType.Name)
            {
                case "Int16":
                case "Int32":
                case "Int64":
                    return string.Concat(" CAST(", value, " AS SIGNED) ");
                case "UInt16":
                case "UInt32":
                case "UInt64":
                case "Boolean":
                    return string.Concat(" CAST(", value, " AS UNSIGNED) ");
                case "Decimal":
                    return string.Concat(" CAST(", value, " AS DECIMAL(18,3)) ");
                case "Single":
                    return string.Concat(" CAST(", value, " AS DECIMAL(32,20)) ");
                case "Double":
                    return string.Concat(" CAST(", value, " AS DECIMAL(64,20)) ");
                case "String":
                    return string.Concat(" CAST(", value, " AS CHAR) ");
                default:
                    throw new NotSupportedException("不支持转换:" + convertType.Name);
            }
        }

        public override string MemberInMember(Type type, string memberName, string value)
        {
            if (type.Equals(typeof(string)))
            {
                switch (memberName)
                {
                    case "Length":
                        return string.Format("LENGTH({0}) ", value);
                    default:
                        throw new MissingMemberException(memberName);
                }
            }
            if (type.Equals(typeof(DateTime)))
            {
                string funName;
                switch (memberName)
                {
                    case "Year":
                        funName = "YEAR";
                        break;
                    case "Month":
                        funName = "MONTH";
                        break;
                    case "DayOfWeek":
                        funName = "WEEK";
                        break;
                    case "Day":
                        funName = "DAY";
                        break;
                    case "Hour":
                        funName = "HOUR";
                        break;
                    case "Minute":
                        funName = "MINUTE";
                        break;
                    case "Second":
                        funName = "SECOND";
                        break;
                    case "Millisecond":
                        funName = "MICROSECOND";
                        break;
                    default:
                        throw new MissingMemberException(memberName);
                }
                return string.Concat("EXTRACT(", funName, " from ", value, ")");
            }
            return base.MemberInMember(type, memberName, value);
        }
        public override string SqlFunction(string funName, MethodInfo method, CallArguments callArguments)
        {
            if (method != null)
            {
                #region String 方法
                if (method.ReflectedType.Equals(typeof(string)))
                {
                    switch (funName)
                    {
                        case "Contains":
                            return string.Format("{0} LIKE CONCAT('%',{1},'%')", callArguments.GetValue(0), callArguments.GetValue(1));
                        case "StartsWith":
                            return string.Format("{0} LIKE  CONCAT({1},'%')", callArguments.GetValue(0), callArguments.GetValue(1));
                        case "EndsWith":
                            return string.Format("{0} LIKE  CONCAT('%',{1})", callArguments.GetValue(0), callArguments.GetValue(1));
                        case "IsNullOrWhiteSpace":
                            return string.Format("LENGTH(TRIM(IFNULL({0},'')))<1", callArguments.GetValue(0));
                        case "IsNullOrEmpty":
                            return string.Format("LENGTH(IFNULL({0},''))<1", callArguments.GetValue(0));
                        case "IndexOf":
                            // Mysql 索引 修正
                            return string.Format("( LOCATE({0},{1}) - 1 ) ", callArguments.GetValue(1), callArguments.GetValue(0));
                        case "Concat":
                            return string.Concat("CONCAT(" + string.Join(",", callArguments.GetValues()) + ")");
                        case "Substring":
                            // Mysql 索引 修正
                            if (callArguments.Length == 3)
                                return string.Format(" SUBSTRING({0},{1} + 1,{2}) ", callArguments.GetValue(0), callArguments.GetValue(1), callArguments.GetValue(2));
                            return string.Format(" SUBSTRING({0},{1} + 1) ", callArguments.GetValue(0), callArguments.GetValue(1));
                        case "Replace":
                            return string.Format(" REPLACE({0},{1},{2}) ", callArguments.GetValue(0), callArguments.GetValue(1), callArguments.GetValue(2));
                        case "Format":
                            return string.Format(callArguments.GetValue(0), callArguments.GetValues().Skip(1).ToArray());
                        default:
                            break;
                    }
                }

                #endregion

                #region Math  方法
                if (method.ReflectedType.Equals(typeof(Math)))
                {
                    switch (funName)
                    {
                        case "Round":
                            if (callArguments.Length == 2)
                            {
                                return string.Format("ROUND({0},{1})", callArguments.GetValue(0), callArguments.GetValue(1));
                            }
                            return string.Format("ROUND({0})", callArguments.GetValue(0));
                        case "Ceiling":
                        case "Floor":
                        case "Abs":
                            return string.Format("{0}({1})", funName.ToUpper(), callArguments.GetValue(0));
                        case "Max":
                            return string.Format("GREATEST({0})", string.Join(",", callArguments.GetValues()));
                        case "Min":
                            return string.Format("LEAST({0})", string.Join(",", callArguments.GetValues()));
                        default:
                            break;
                    }
                }
                #endregion
            }
            switch (funName)
            {
                case "Between":
                    return string.Format("{0} BETWEEN {1} AND {2}", callArguments.GetValue(0), callArguments.GetValue(1), callArguments.GetValue(2));

                case "FindInSet":
                    // Mysql 索引 修正
                    return string.Format(" (FIND_IN_SET({0},{1}) - 1)", callArguments.GetValue(0), callArguments.GetValue(1));
                case "Rand":
                    return "Rand()";
                case "LongCount":
                case "Count":
                    return string.Format("COUNT({0})", "1");
                case "Contains":
                    if (typeof(JToken).IsAssignableFrom(method.ReflectedType))
                    {
                        return string.Format("{0} LIKE CONCAT({1},'%')", callArguments.GetValue(0), callArguments.GetValue(1));
                    }
                    if (callArguments.Expressions[1] is LambdaExpression)
                    {
                        throw new NotImplementedException("未实现的方法：" + funName);
                    }
                    else
                    {
                        return string.Format("{0} IN({1})", callArguments.GetValue(1), callArguments.GetValue(0));
                    }
                case "Max":
                case "Min":
                case "Sum":
                    return string.Format("{0}({1})", funName.ToUpper(), callArguments.GetValue(0));
                case "Average":
                    return string.Format("AVG({0})", callArguments.GetValue(0));
                default:
                    break;
            }
            throw new NotImplementedException("未实现的方法：" + funName);
        }

        public override string QueryIdentity => ";SELECT @@IDENTITY";

        public override string BuildNoQuerySql(string methodName)
        {
            switch (methodName)
            {
                case "Add":
                    return $"INSERT INTO {FormTable} ({string.Join(",", GetInsertColumns())}) VALUES ({string.Join(",", GetInsertValues(ConstantObj))}) ";
                case "AddRange":
                    StringBuilder sqlInser = new StringBuilder();
                    sqlInser.Append($"INSERT INTO {FormTable} ({string.Join(",", GetInsertColumns())}) VALUES ");
                    foreach (var item in ConstantObj as IEnumerable)
                    {
                        sqlInser.Append($" ({string.Join(",", GetInsertValues(item))}),");
                    }
                    return sqlInser.ToString().TrimEnd(',');
                case "Update":
                    return $"UPDATE {FormTable} SET {GetSetSql()} {GetWhereSql()} {GetOrderSql()}{GetLimitSql()} ";
                case "UpdateColumns":
                    return $"UPDATE {FormTable} SET {GetSetSql()} {GetWhereSql()} {GetOrderSql()}{GetLimitSql()} ";
                case "Delete":
                    return $"DELETE FROM {FormTable} {GetWhereSql()} {GetOrderSql()}{GetLimitSql()} ";
                default:
                    throw new NotImplementedException("未实现的方法：" + methodName);
            }
        }


        public override string BuildSql(bool isEasy = false)
        {

            if (isEasy && Selects.Count == 0 && Wheres.Count == 0 && Orders.Count == 0 && Groups.Count == 0)
            {
                return FormTable;
            }
            string sql;
            if (IsAverage && (Limit > 0 || Offset > 0))
            { // 特殊情况再包裹一层
                sql = $"SELECT {GetSelectSql()} FROM (SELECT * FROM  {FormTable}{GetWhereSql()}{GetGroupSql()}{GetOrderSql()}{GetLimitSql()}）AS TS";
            }
            else
            {
                sql = $"SELECT {GetSelectSql()} FROM {FormTable}{GetWhereSql()}{GetGroupSql()}{GetOrderSql()}{GetLimitSql()}";
            }
            if (isEasy)
            {
                return $"({sql})";
            }
            if (IsForUpdate)
            {
                return $"{sql} FOR UPDATE";
            }
            return sql;
        }

        private string GetSetSql()
        {
            return string.Join(",", Sets.Select(a => $"{a.Item1}={a.Item2}"));
        }

        private string GetSelectSql()
        {
            return $"{string.Format(SelectFormat, Selects.Count == 0 ? "*" : string.Join(",", Selects)) }";
        }

        private string GetGroupSql()
        {
            if (Groups.Count == 0)
            {
                return "";
            }
            if (Havings.Count == 0)
            {
                return " GROUP BY " + string.Join(",", Groups);
            }
            return " GROUP BY " + string.Join(",", Groups) + " HAVING " + string.Join(" AND ", Havings);
        }

        private string GetWhereSql()
        {
            if (Wheres.Count == 0)
            {
                return "";
            }
            return " WHERE " + string.Join(" AND ", Wheres);
        }
        private string GetOrderSql()
        {
            // 聚合函数排序也不生效
            if (Orders.Count == 0 || IsAverage) return "";
            return " ORDER BY " + string.Join(",", Orders);
        }

        private string GetLimitSql()
        {
            if (Limit > 0)
            {
                if (Offset > 0)
                {
                    return string.Format(" LIMIT {0},{1}", Offset, Limit);
                }
                return string.Format(" LIMIT {0}", Limit);
            }
            return "";
        }

    }
}
